﻿/************************************************************/
/*****	Articles Install Script     			        *****/
/*****  =======================        		            *****/
/*****                                                  *****/
/*****  Version:	1.0.0                   			*****/
/*****	Requires:	DotNetNuke 4.6.0 or above			*****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/* Section Table */
/*****************/

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticleSection]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}ArticleSection]
			(
				[SectionID] [int] IDENTITY(1,1) NOT NULL,
				[ModuleID] [int] NOT NULL,
				[ParentSectionID] [int] NULL,
				[Name] [nvarchar](50) NOT NULL,
				[SectionSkin] [nvarchar](200) NULL,
				[DefaultImageDirectory] int NULL,
				[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_{objectQualifier}ArticleSection_IsDeleted]  DEFAULT ((0)),
				[ViewOrder] [int] NOT NULL CONSTRAINT [DF_{objectQualifier}ArticleSection_ViewOrder]  DEFAULT (0)
			)

		ALTER TABLE {databaseOwner}[{objectQualifier}ArticleSection] ADD 
			CONSTRAINT [PK_{objectQualifier}ArticleSection] PRIMARY KEY CLUSTERED  ([SectionID])
		
		ALTER TABLE {databaseOwner}[{objectQualifier}ArticleSection]  WITH NOCHECK ADD  
			CONSTRAINT [FK_{objectQualifier}ArticleSection_{objectQualifier}ArticleSection] FOREIGN KEY([ParentSectionID]) REFERENCES {databaseOwner}[{objectQualifier}ArticleSection] ([SectionID])

		ALTER TABLE {databaseOwner}[{objectQualifier}ArticleSection]  WITH NOCHECK ADD  
			CONSTRAINT [FK_{objectQualifier}ArticleSection_{objectQualifier}Modules] FOREIGN KEY([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE

		CREATE NONCLUSTERED INDEX [IX_{objectQualifier}ArticleSection_Name] ON {databaseOwner}[{objectQualifier}ArticleSection] ([Name])
	END

GO

/* Article Table */
/*****************/

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}Article]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}Article]
			(
				[ArticleID] [int] IDENTITY(1,1) NOT NULL,
				[VersionNo] [int] NOT NULL CONSTRAINT [DF_{objectQualifier}Article_VersionNo]  DEFAULT ((1)),
				[ModuleID] [int] NOT NULL,
				[SectionID] [int] NULL,
				[Title] [nvarchar](100) NOT NULL,
				[SubTitle] [nvarchar](100) NULL,
				[Abstract] [nvarchar](2000) NULL,
				[AbstractImage] [int] NULL,
				[Body] [ntext] NULL,
				[Author] [nvarchar](100) NULL,
				[Status] [int] NOT NULL CONSTRAINT [DF_{objectQualifier}Article_Status]  DEFAULT ((0)),
				[ViewOrder] [int] NOT NULL CONSTRAINT [DF_{objectQualifier}Article_ViewOrder]  DEFAULT (0)
			)

		ALTER TABLE {databaseOwner}[{objectQualifier}Article] ADD 
			CONSTRAINT [PK_{objectQualifier}Article] PRIMARY KEY CLUSTERED ( [ArticleID] ASC, [VersionNo] ASC )
		ALTER TABLE {databaseOwner}[{objectQualifier}Article]  WITH NOCHECK ADD  
			CONSTRAINT [FK_{objectQualifier}Article_{objectQualifier}ArticleSection] FOREIGN KEY([SectionID]) REFERENCES {databaseOwner}[{objectQualifier}ArticleSection] ([SectionID])

		ALTER TABLE {databaseOwner}[{objectQualifier}Article]  WITH NOCHECK ADD  
			CONSTRAINT [FK_{objectQualifier}Article_{objectQualifier}Modules] FOREIGN KEY([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE
	END

GO

/* Create ArticleSections View */
/*******************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}vw_ArticleSections]') AND OBJECTPROPERTY(id,N'IsView') = 1)
	DROP VIEW {databaseOwner}[{objectQualifier}vw_ArticleSections]
GO

CREATE VIEW {databaseOwner}[{objectQualifier}vw_ArticleSections]
AS
	SELECT     
		SectionID, 
		ModuleID, 
		ParentSectionID, 
		[Name], 
		SectionSkin, 
		DefaultImageDirectory, 
		ViewOrder,
		IsDeleted,
		(SELECT AuditUser FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'ArticleSection') AND (AuditType = 0) AND (ItemID = S.SectionID)) AS CreatedByUser,
		(SELECT AuditDate FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'ArticleSection') AND (AuditType = 0) AND (ItemID = S.SectionID)) AS CreatedDate,
		(SELECT TOP 1 AuditUser FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'ArticleSection') AND (AuditType > 0) AND (ItemID = S.SectionID) ORDER BY AuditDate DESC) AS UpdatedByUser,
		(SELECT TOP 1 AuditDate FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'ArticleSection') AND (AuditType > 0) AND (ItemID = S.SectionID) ORDER BY AuditDate DESC) AS UpdatedDate
	FROM dbo.{objectQualifier}ArticleSection AS S

GO

/* Create Articles View */
/************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}vw_Articles]') AND OBJECTPROPERTY(id,N'IsView') = 1)
	DROP VIEW {databaseOwner}[{objectQualifier}vw_Articles]
GO

CREATE VIEW {databaseOwner}[{objectQualifier}vw_Articles]
AS
	SELECT
		ArticleID, 
		ModuleID, 
		SectionID, 
		VersionNo,
		Title, 
		SubTitle, 
		Author, 
		Abstract, 
		AbstractImage, 
		Body, 
		Status,
		ViewOrder, 
		(SELECT AuditUser FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'Article') AND (AuditType = 0) AND (ItemID = A.ArticleID)) AS CreatedByUser,
		(SELECT AuditDate FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'Article') AND (AuditType = 0) AND (ItemID = A.ArticleID)) AS CreatedDate,
		(SELECT TOP 1 AuditUser FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'Article') AND (AuditType = 5 OR AuditType = 1) AND (ItemID = A.ArticleID) AND (VersionNo = A.VersionNo) ORDER BY AuditDate DESC) AS UpdatedByUser,
		(SELECT TOP 1 AuditDate FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'Article') AND (AuditType = 5 OR AuditType = 1) AND (ItemID = A.ArticleID) AND (VersionNo = A.VersionNo) ORDER BY AuditDate DESC) AS UpdatedDate, 
        (SELECT TOP 1 AuditType FROM dbo.{objectQualifier}Toolkit_AuditInfo WHERE (AuditTable = 'Article') AND (ItemID = A.ArticleID) AND (VersionNo = A.VersionNo) ORDER BY AuditDate DESC) AS AuditType
	FROM dbo.{objectQualifier}Article AS A

GO

/* CreateSection Procedure */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesCreateSection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesCreateSection]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesCreateSection]
(
	@ModuleID				int,
	@Name					nvarchar (50),
	@ParentSectionID		int,
	@SectionSkin			nvarchar(200),
	@DefaultImageDirectory	nvarchar(200),
	@ViewOrder				int,
	@UserID					int
)
AS
	INSERT INTO {objectQualifier}ArticleSection	
		(
			ModuleID,
			[Name],
			ParentSectionID,
			SectionSkin,
			DefaultImageDirectory,
			ViewOrder
		)
		VALUES		
			(
				@ModuleID,
				@Name,
				@ParentSectionID,
				@SectionSkin,
				@DefaultImageDirectory,
				@ViewOrder
			)

	DECLARE @SectionID int
	SET @SectionID = @@IDENTITY

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'ArticleSection', @SectionID, 1, 0, '', @UserID

	SELECT @SectionID

GO

/* DeleteSection Procedure */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesDeleteSection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesDeleteSection]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesDeleteSection]
(
	@SectionID	int,
	@UserID		int
)
AS
	UPDATE {objectQualifier}ArticleSection
		SET
			IsDeleted = 1
		WHERE SectionID = @SectionID
	
	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'ArticleSection', @SectionID, 1, -1, '', @UserID
	
GO

/* GetSection Procedure */
/************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetSection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetSection]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetSection] 
(
	@SectionID	int
)
AS
	SELECT *
		FROM {objectQualifier}vw_ArticleSections
		WHERE SectionID = @SectionID
		
GO

/* GetSections Procedure */
/*************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetSections]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetSections]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetSections] 
(
	@ModuleID	int
)
AS
	SELECT *
		FROM {objectQualifier}vw_ArticleSections
		WHERE ModuleID = @ModuleID
			AND IsDeleted = 0
		ORDER BY ViewOrder
		
GO

/* GetSectionsByParentSection Procedure */
/****************************************/

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetSectionsByParentSection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetSectionsByParentSection]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetSectionsByParentSection] 
(
	@ModuleID	int,
	@SectionID	int
)
AS
	SELECT *
		FROM {objectQualifier}vw_ArticleSections
		WHERE ParentSectionID = @SectionID OR (ParentSectionID IS NULL and @SectionID IS NULL)
			AND ModuleID = @ModuleID
			AND IsDeleted = 0
		ORDER By ViewOrder
		
GO

/* UpdateSection Procedure */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesUpdateSection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesUpdateSection]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesUpdateSection]
(
	@SectionID				int,
	@Name					nvarchar(50),
	@ParentSectionID		int,
	@SectionSkin			nvarchar(200),
	@DefaultImageDirectory	nvarchar(200),
	@ViewOrder				int,
	@UserID					int
)
AS
	UPDATE {objectQualifier}ArticleSection
		SET
			[Name] = @Name,
			ParentSectionID = @ParentSectionID,
			SectionSkin = @SectionSkin,
			DefaultImageDirectory = @DefaultImageDirectory,
			ViewOrder = @ViewOrder
		WHERE SectionID = @SectionID

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'ArticleSection', @SectionID, 1, 1, '', @UserID
	
GO

/* CreateArticle */
/*****************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesCreateArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesCreateArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesCreateArticle]
(
	@ModuleID		int,
	@SectionID		int,
	@Author			nvarchar (100),
	@Title			nvarchar (100),
	@SubTitle 		nvarchar (100),
	@Abstract		nvarchar (2000),
	@AbstractImage	int,
	@Body			ntext,
	@Status			int,
	@ViewOrder		int,
	@Comment		ntext,
	@UserID			int
)
AS
	DECLARE @ArticleID int
	SET @ArticleID = (SELECT MAX(ArticleID) + 1 FROM {objectQualifier}Article)
	IF @ArticleID IS NULL
		SET @ArticleID = 1

	INSERT INTO dbo.{objectQualifier}Article	
		(
			ArticleID,
			VersionNo,
			ModuleID,
			SectionID,
			Author,
			Title,
			SubTitle,
			Abstract,
			AbstractImage,
			Body,
			Status,
			ViewOrder
		)
		VALUES			
			(
				@ArticleID,
				1,
				@ModuleID,
				@SectionID,
				@Author,
				@Title,
				@SubTitle,
				@Abstract,
				@AbstractImage,
				@Body,
				@Status,
				{databaseOwner}{objectQualifier}ArticlesGetNextViewOrder(@SectionID, @ViewOrder)
			)

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, 1, 0, @Comment, @UserID

	SELECT @ArticleID

GO

/* CreateNewVersion */
/********************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesCreateNewVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesCreateNewVersion]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesCreateNewVersion]
(
	@ArticleID		int,
	@VersionNo		int,
	@ModuleID		int,
	@SectionID		int,
	@Author			nvarchar (100),
	@Title			nvarchar (100),
	@SubTitle		nvarchar (100),
	@Abstract		nvarchar (2000),
	@AbstractImage	int,
	@Body			ntext,
	@ViewOrder		int,
	@Comment		ntext,
	@UserID			int
)
AS
	INSERT INTO dbo.{objectQualifier}Article	
		(
			ArticleID,
			VersionNo,
			ModuleID,
			SectionID,
			Author,
			Title,
			SubTitle,
			Abstract,
			AbstractImage,
			Body,
			Status,
			ViewOrder
		)
		VALUES			
			(
				@ArticleID,
				@VersionNo,
				@ModuleID,
				@SectionID,
				@Author,
				@Title,
				@SubTitle,
				@Abstract,
				@AbstractImage,
				@Body,
				0,
				@ViewOrder
			)

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, 2, @Comment, @UserID

GO

/* DeleteArticle */
/*****************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesDeleteArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesDeleteArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesDeleteArticle]
(
	@ArticleID	int,
	@UserID		int
)
AS
	UPDATE {objectQualifier}Article
		SET
			Status = 999
		WHERE ArticleID = @ArticleID

	DECLARE @VersionNo int
	SET @VersionNo = (SELECT TOP 1 VersionNo FROM {objectQualifier}Article WHERE ArticleID = @ArticleID ORDER BY VersionNo Desc)
	
	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, -1, '', @UserID

GO

/* GetArticle */
/**************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticle]
(
	@ArticleID	int
)
AS
	SELECT TOP 1 * 
		FROM {objectQualifier}vw_Articles
		WHERE ArticleID = @ArticleID
		ORDER BY VersionNo DESC
GO

/* GetArticles Procedure */
/*************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticles]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticles]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticles]
(
	@ModuleID int
)
AS
	SELECT * 
		FROM {objectQualifier}vw_Articles
		WHERE ModuleID = @ModuleID
			AND Status = 2
		ORDER BY ViewOrder ASC
GO

/* GetArticlesByPage Procedure */
/*******************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticlesByPage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByPage]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByPage]
(
	@ModuleID		int,
    @PageIndex      int,
    @PageSize       int
)
AS
	BEGIN
		-- Set the page bounds
		DECLARE @PageLowerBound INT
		DECLARE @PageUpperBound INT
		SET @PageLowerBound = @PageSize * @PageIndex
		SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

		-- Create a temp table TO store the select results
		CREATE TABLE #PageIndex
		(
			IndexId int IDENTITY (0, 1) NOT NULL,
			ArticleId int,
			VersionNo int
		)

		-- Insert into our temp table
		INSERT INTO #PageIndex (ArticleId, VersionNo)
			SELECT ArticleId, VersionNo
				FROM  {objectQualifier}vw_Articles 
				WHERE ModuleID = @ModuleID
					AND Status = 2
				ORDER BY ViewOrder ASC

		SELECT  a.*
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
			AND Status = 2
		ORDER BY ViewOrder ASC

		SELECT  TotalRecords = COUNT(*)
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  Status = 2

	END

GO

/* GetArticlesBySection Procedure */
/**********************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticlesBySection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesBySection]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesBySection]
(
	@ModuleID	int,
	@SectionID	int
)
AS
	SELECT * 
		FROM {objectQualifier}vw_Articles
		WHERE (SectionID = @SectionID OR (SectionID IS NULL and @SectionID IS NULL))
			AND ModuleID = @ModuleID
			AND Status = 2
		ORDER BY ViewOrder ASC

GO

/* GetArticlesBySectionByPage Procedure */
/****************************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticlesBySectionByPage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesBySectionByPage]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesBySectionByPage]

	@ModuleID	int,
	@SectionID		int,
    @PageIndex      int,
    @PageSize       int

AS
	BEGIN
		-- Set the page bounds
		DECLARE @PageLowerBound INT
		DECLARE @PageUpperBound INT
		SET @PageLowerBound = @PageSize * @PageIndex
		SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

		-- Create a temp table TO store the select results
		CREATE TABLE #PageIndex
		(
			IndexId int IDENTITY (0, 1) NOT NULL,
			ArticleId int,
			VersionNo int
		)

		-- Insert into our temp table
		INSERT INTO #PageIndex (ArticleId, VersionNo)
			SELECT ArticleId, VersionNo
				FROM  {objectQualifier}vw_Articles 
				WHERE (SectionID = @SectionID OR (SectionID IS NULL and @SectionID IS NULL))
					AND ModuleID = @ModuleID
					AND Status = 2
				ORDER BY ViewOrder ASC

		SELECT  a.*
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
			AND Status = 2
		ORDER BY ViewOrder ASC

		SELECT  TotalRecords = COUNT(*)
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  Status = 2

	END

GO

/* GetArticlesByCreatorAndStatus Procedure */
/*******************************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorAndStatus]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorAndStatus]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorAndStatus]
(
	@ModuleID	int,
	@UserID		int,
	@Status		int
)
AS
	SELECT * 
		FROM {objectQualifier}vw_Articles
		WHERE  ModuleID = @ModuleID
			AND (CreatedByUser = @UserID OR @UserID IS NULL)
			AND (Status = @Status OR @Status IS NULL)
		ORDER BY ViewOrder ASC

GO

/* GetArticlesByCreatorAndStatusByPage Procedure */
/*************************************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorByPage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorByPage]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorByPage]

	@ModuleID		int,
	@UserID			int,
	@Status			int,
    @PageIndex      int,
    @PageSize       int

AS
	BEGIN
		-- Set the page bounds
		DECLARE @PageLowerBound INT
		DECLARE @PageUpperBound INT
		SET @PageLowerBound = @PageSize * @PageIndex
		SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

		-- Create a temp table TO store the select results
		CREATE TABLE #PageIndex
		(
			IndexId int IDENTITY (0, 1) NOT NULL,
			ArticleId int,
			VersionNo int
		)

		-- Insert into our temp table
		INSERT INTO #PageIndex (ArticleId, VersionNo)
			SELECT ArticleId, VersionNo
				FROM  {objectQualifier}vw_Articles 
				WHERE  ModuleID = @ModuleID
					AND (CreatedByUser = @UserID OR @UserID IS NULL)
					AND (Status = @Status OR @Status IS NULL)
				ORDER BY ViewOrder ASC

		SELECT  a.*
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
			AND (CreatedByUser = @UserID OR @UserID IS NULL)
			AND (Status = @Status OR @Status IS NULL)
		ORDER BY ViewOrder ASC

		SELECT  TotalRecords = COUNT(*)
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  (CreatedByUser = @UserID OR @UserID IS NULL)
			AND (Status = @Status OR @Status IS NULL)

	END

GO

/* GetCurrentArticles Procedure */
/********************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetCurrentArticles]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetCurrentArticles]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetCurrentArticles]
(
	@ModuleID	int
)
AS
	DECLARE @SectionID int
	SET @SectionID =	( SELECT TOP 1 SectionID FROM {objectQualifier}vw_ArticleSections
						WHERE ModuleID = @ModuleID
						ORDER BY [CreatedDate] DESC
					)

	SELECT *
		FROM {objectQualifier}vw_Articles
		WHERE SectionID = @SectionID OR (SectionID IS NULL AND @SectionID IS NULL)
		ORDER BY ViewOrder ASC

GO

/* GetPublishedArticle Procedure */
/*********************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetPublishedArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetPublishedArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetPublishedArticle]
(
	@ArticleID	int
)
AS
	SELECT TOP 1 * 
		FROM {objectQualifier}vw_Articles
		WHERE ArticleID = @ArticleID
			AND Status = 2
		ORDER BY VersionNo DESC
GO

/* GetArticlesByCreatorAndStatusByPage Procedure */
/*************************************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorAndStatusByPage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorAndStatusByPage]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesGetArticlesByCreatorAndStatusByPage]
(
	@ModuleID		int,
	@UserID			int,
	@Status			int,
    @PageIndex      int,
    @PageSize       int
)
AS
	BEGIN
		-- Set the page bounds
		DECLARE @PageLowerBound INT
		DECLARE @PageUpperBound INT
		SET @PageLowerBound = @PageSize * @PageIndex
		SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

		-- Create a temp table TO store the select results
		CREATE TABLE #PageIndex
		(
			IndexId int IDENTITY (0, 1) NOT NULL,
			ArticleId int,
			VersionNo int
		)

		-- Insert into our temp table
		INSERT INTO #PageIndex (ArticleId, VersionNo)
			SELECT ArticleId, VersionNo
				FROM  {objectQualifier}vw_Articles 
				WHERE  ModuleID = @ModuleID
					AND (CreatedByUser = @UserID OR @UserID IS NULL)
					AND (Status = @Status OR @Status IS NULL)
				ORDER BY ViewOrder ASC

		SELECT  a.*
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
			AND (CreatedByUser = @UserID OR @UserID IS NULL)
			AND (Status = @Status OR @Status IS NULL)
		ORDER BY ViewOrder ASC

		SELECT  TotalRecords = COUNT(*)
		FROM	{objectQualifier}vw_Articles a 
			INNER JOIN	#PageIndex p ON a.ArticleId = p.ArticleId AND a.VersionNo = p.VersionNo
		WHERE  (CreatedByUser = @UserID OR @UserID IS NULL)
			AND (Status = @Status OR @Status IS NULL)

	END
GO

/* PublishArticle Procedure */
/****************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesPublishArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesPublishArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesPublishArticle]
(
	@ArticleID		int,
	@VersionNo		int,
	@Comment		ntext,
	@UserID			int
)
AS
	UPDATE {objectQualifier}Article
		SET
			Status = 2
		WHERE ArticleID = @ArticleID
			AND VersionNo = @VersionNo

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, 5, @Comment, @UserID
	
GO

/* RejectArticle Procedure */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesRejectArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesRejectArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesRejectArticle]
(
	@ArticleID		int,
	@VersionNo		int,
	@Comment		ntext,
	@UserID			int
)
AS
	UPDATE {objectQualifier}Article
		SET
			Status = 0
		WHERE ArticleID = @ArticleID
			AND VersionNo = @VersionNo

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, 4, @Comment, @UserID
	
GO

/* RestoreArticle Procedure */
/****************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesRestoreArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesRestoreArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesRestoreArticle]
(
	@ArticleID		int,
	@VersionNo		int,
	@Status			int,
	@Comment		ntext,
	@UserID			int
)
AS
	UPDATE {objectQualifier}Article
		SET
			Status = @Status
		WHERE ArticleID = @ArticleID
			AND VersionNo = @VersionNo

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, 6, @Comment, @UserID
	
GO

/* SubmitArticle Procedure */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesSubmitArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesSubmitArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesSubmitArticle]
(
	@ArticleID		int,
	@VersionNo		int,
	@Comment		ntext,
	@UserID			int
)
AS
	UPDATE {objectQualifier}Article
		SET
			Status = 1
		WHERE ArticleID = @ArticleID
			AND VersionNo = @VersionNo

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, 3, @Comment, @UserID
	
GO

/* UpdateArticle Procedure */
/***************************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesUpdateArticle]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}ArticlesUpdateArticle]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}ArticlesUpdateArticle]
(
	@ArticleID		int,
	@VersionNo		int,
	@SectionID		int,
	@Author			nvarchar (100),
	@Title			nvarchar (100),
	@SubTitle		nvarchar (100),
	@Abstract		nvarchar (2000),
	@AbstractImage	int,
	@Body			ntext,
	@Status			int,
	@ViewOrder		int,
	@Comment		ntext,
	@UserID			int
)
AS
	UPDATE {objectQualifier}Article
		SET
			Title = @Title,
			SubTitle = @SubTitle,
			Author = @Author,
			Abstract = @Abstract,
			AbstractImage = @AbstractImage,
			Body = @Body,
			Status = @Status,
			ViewOrder = @ViewOrder,
			SectionID = @SectionID
		WHERE ArticleID = @ArticleID
			AND VersionNo = @VersionNo

	/*Add Audit Info  */
	EXEC {databaseOwner}[{objectQualifier}Toolkit_CreateAuditInfo] N'Article', @ArticleID, @VersionNo, 1, @Comment, @UserID

	
GO

/* GetNextViewOrder */
/********************/

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}ArticlesGetNextViewOrder]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
	DROP FUNCTION {databaseOwner}[{objectQualifier}ArticlesGetNextViewOrder]
GO

CREATE FUNCTION {databaseOwner}[{objectQualifier}ArticlesGetNextViewOrder] 
(
	@SectionID	int,
	@ViewOrder	int
)

RETURNS INT

AS
	BEGIN

		DECLARE @ReturnValue int
		
		IF @ViewOrder > -1
			BEGIN
				SET @ReturnValue = @ViewOrder
			END
		ELSE
			BEGIN
				SET @ReturnValue = (SELECT TOP 1 ViewOrder 
									FROM {objectQualifier}Article
									WHERE SectionID = @SectionID
									ORDER BY ViewOrder DESC) + 2
				IF @ReturnValue IS NULL
					BEGIN
						SET @ReturnValue = 1
					END
			END

		RETURN @ReturnValue
	END

GO

/* Add Article Permissions */
/***************************/

/*
DECLARE @ModuleDefID int
SET @ModuleDefID = (SELECT ModuleDefID from {objectQualifier}ModuleDefinitions WHERE FriendlyName = 'DNN_Articles')

IF (SELECT COUNT(*) FROM dbo.{objectQualifier}Permission WHERE PermissionCode = 'ARTICLES' And PermissionKey = 'ARTICLECREATE' ) = 0
	BEGIN
		INSERT INTO dbo.{objectQualifier}Permission
			( PermissionCode, ModuleDefID, PermissionKey, PermissionName )
			VALUES ( 'ARTICLES', @ModuleDefID,  'CREATE', 'Create Article')
	END

IF (SELECT COUNT(*) FROM dbo.{objectQualifier}Permission WHERE PermissionCode = 'ARTICLES' And PermissionKey = 'ARTICLEMODERATE' ) = 0
	BEGIN
		INSERT INTO dbo.{objectQualifier}Permission
			( PermissionCode, ModuleDefID, PermissionKey, PermissionName )
			VALUES ( 'ARTICLES', @ModuleDefID,  'MODERATE', 'Moderate Article')
	END

IF (SELECT COUNT(*) FROM dbo.{objectQualifier}Permission WHERE PermissionCode = 'ARTICLES' And PermissionKey = 'ARTICLEDELETE' ) = 0
	BEGIN
		INSERT INTO dbo.{objectQualifier}Permission
			( PermissionCode, ModuleDefID, PermissionKey, PermissionName )
			VALUES ( 'ARTICLES', @ModuleDefID,  'DELETE', 'Delete Article')
	END
	
GO
*/

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/
